﻿use master 
go
if exists(select * from sys.databases where name='DBCurricula')
drop database DBCurricula
go
create database DBCurricula
go
use DBCurricula
go
create table TitleName     --职称名称
(
TitleID int primary key identity(1,1),
Name nvarchar(50) not null,
IsDeleted bit not null
)
create table CategoryName     --专业类别
(
CategoryID int primary key identity(1,1),
Name nvarchar(50) not null,
IsDeleted bit not null
)
create table CourseName     --课程名字
(
CourseID int primary key identity(1,1),
Name nvarchar(50) not null,
IsDeleted bit not null
)
create table StudentInfo    --学生
(
ID int primary key identity(1,1),
Name nvarchar(50) not null,
SNo int not null,
Sex int not null CHECK(Sex=1 OR Sex=2),--性别1:男；2:女
PhoneNum varchar(20) not null,
Password  varchar(100) not null,
Pwd varchar(100) not null,
Address varchar(100) not null,
IsDeleted bit not null
)
go

create table Professor   --教授
(
ID int primary key identity(1,1),
Name varchar(20) not null,--姓名
Avatar varchar(100) not null,--头像照片
Sex int not null CHECK(Sex=1 OR Sex=2),--性别1:男；2:女
CategoryName int references CategoryName(CategoryID) not null,--专业类别
CourseName int references CourseName(CourseID) not null,--课程名字
TitleName int references TitleName(TitleID) not null,--职称名称 助教、讲师、副教授、教授
CreateDateTime datetime default(getdate()) not null,--上课时间
IsDeleted bit not null
)
go
insert into TitleName values
('助教',0),('讲师',0),('副教授',0),('教授',0),('高级教授',0)
insert into CategoryName values
('哲学',0),('经济学',0),('教育学',0),('历史学',0),('艺术学',0)
insert into CourseName values
('人生哲学',0),('西方经济学',0),('教育学基础',0),('地理历史学',0),('音乐学',0)
insert into StudentInfo values
('张三','00001',1,'18888888888','123456','123456','河南省郑州市',0),
('李四','00002',1,'18888888888','123456','123456','河南省郑州市',0),
('王五','00003',2,'18888888888','123456','123456','河南省郑州市',0),
('赵六','00004',1,'18888888888','123456','123456','河南省郑州市',0),
('钱七','00005',2,'18888888888','123456','123456','河南省郑州市',0)
insert into Professor values
('张教授','/avatar/41aec92634d34970a32ba51fe1258d58.jpg',1,1,1,1,getdate(),0),
('李教授','/avatar/41aec92634d34970a32ba51fe1258d58.jpg',2,2,2,2,getdate(),0),
('王教授','/avatar/41aec92634d34970a32ba51fe1258d58.jpg',1,3,3,3,getdate(),0),
('刘教授','/avatar/41aec92634d34970a32ba51fe1258d58.jpg',2,4,4,4,getdate(),0),
('孙教授','/avatar/41aec92634d34970a32ba51fe1258d58.jpg',1,5,5,2,getdate(),0)
select * from TitleName
select * from CategoryName
select * from CourseName
select * from StudentInfo
select * from Professor